import datetime

from sqlalchemy import Column, DateTime, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()  # 基本映射类


class Device(Base):
    # 定义表名
    __tablename__ = "Device"
    # 定义字段
    mac = Column(String(50), primary_key=True)
    ip = Column(String(50))
    time = Column(
        DateTime, primary_key=True
    )  # 上线时间 2022-04-29 16:02:53.788821  .strftime("%Y-%m-%d %H:%M:%S") 试过去掉后六位不行

    def __init__(self, mac, ip, time):
        self.mac = mac
        self.ip = ip
        self.time = time


class TimeError(Base):
    __tablename__ = "TimeError"
    mac = Column(String(30), primary_key=True)
    time = Column(
        String(30), primary_key=True
    )  # 时间段，以小时为分隔 datetime.datetime.now().strftime("%Y-%m-%d %H")
    objcls = Column(String(30), primary_key=True)
    num = Column(Integer())

    def __init__(self, mac, time, objcls, num):
        self.mac = mac
        self.time = time
        self.objcls = objcls
        self.num = num


class Data(Base):
    __tablename__ = "Data"
    mac = Column(String(30), primary_key=True)
    time = Column(
        String(30), primary_key=True
    )  # 时间段，以小时为分隔 datetime.datetime.now().strftime("%Y-%m-%d %H")
    errorNum = Column(Integer())  # 含有错误的图像数量，张
    photoNum = Column(Integer())  # 检查的图片总数，张

    def __init__(self, mac, time, errorNum, photoNum):
        self.mac = mac
        self.time = time
        self.errorNum = errorNum
        self.photoNum = photoNum


if __name__ == "__main__":
    print("--------------DB test--------------")
    engine = create_engine(
        "sqlite:///maxipy.db?check_same_thread=False", echo=False
    )  # 数据库连接
    Base.metadata.create_all(engine)  # 建立表，已有表就不会重新建立了
    DbSession = sessionmaker(bind=engine)  # 创建session
    session = DbSession()  # 建立会话

    # 插入数据测试
    addTest = Device(
        "fc:f5:c4:07:fc:f0",
        "2408:8207:604a:c320:61c7:fd8c:5101:69f3",
        datetime.datetime.now(),
    )
    session.add(addTest)
    session.commit()

    addTest = TimeError(
        "fc:f5:c4:07:fc:f0",
        datetime.datetime.now().strftime("%Y-%m-%d %H"),
        "patches",
        0,
    )
    session.add(addTest)
    session.commit()

    mac = "fc:f5:c4:07:fc:f0"
    init = Data(mac, datetime.datetime.now().strftime("%Y-%m-%d %H"), 0, 0)
    session.add(init)
    session.commit()

    # 查询
    res = session.query(Device).all()
    for r in res:
        print(r.mac, r.ip, r.time)

    # 更新，one()找不到的时候会发生异常
    try:
        res = (
            session.query(Data)
            .filter(
                Data.mac == "fc:f5:c4:07:fc:f0",
                Data.objcls == "asd",
            )
            .one()
        )
        res.num = 1
        session.commit()
    except Exception as e:
        print(e)
